package com.dao.impl;

import com.dao.PMationDao;
import com.db.ConnectionFactory;
import com.entry.Returnn;
import com.entry.Zgoods;
import com.util.PageModel;

import java.sql.*;
import java.util.ArrayList;

public class PMationImpl implements PMationDao {
    @Override
    public PageModel<Zgoods> findSp(int pageSize, int pageNo) {
        ArrayList<Zgoods> list = new ArrayList<Zgoods>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select zid,zname,zcount,gid,sup_name from zgoods";
        PageModel<Zgoods> pm = new PageModel<Zgoods>(pageSize, pageNo, total(sql));
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql+" limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()){
                Zgoods zgoods = new Zgoods();
                zgoods.setZid(rs.getInt(1));
                zgoods.setZname(rs.getString(2));
                zgoods.setZcount(rs.getInt(3));
                zgoods.setGid(rs.getString(4));
                zgoods.setSup_name(rs.getString(5));
                list.add(zgoods);
            }
            pm.setData(list);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    @Override
    public Zgoods findId(String id) {
        Zgoods zgoods = null;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select * from zgoods where zid=? limit 1";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,id);
            rs = pre.executeQuery();
            if(rs.next()){
                zgoods = new Zgoods();
                zgoods.setZid(rs.getInt(1));
                zgoods.setZname(rs.getString(2));
                zgoods.setZcount(rs.getInt(3));
                zgoods.setGid(rs.getString(4));
                zgoods.setSup_name(rs.getString(5));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
                if (rs != null){rs.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return zgoods;
    }

    @Override
    public Zgoods findName(String zname) {
        Zgoods zgoods = null;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select * from zgoods where zname=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,zname);
            rs = pre.executeQuery();
            if(rs.next()){
                zgoods = new Zgoods();
                zgoods.setZid(rs.getInt(1));
                zgoods.setZname(rs.getString(2));
                zgoods.setZcount(rs.getInt(3));
                zgoods.setGid(rs.getString(4));
                zgoods.setSup_name(rs.getString(5));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
                if (rs != null){rs.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return zgoods;
    }

    @Override
    public int update(Zgoods zgoods) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "update zgoods set zname=?,zcount=?,gid=?,sup_name=? where zid=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,zgoods.getZname());
            pre.setInt(2,zgoods.getZcount());
            pre.setString(3,zgoods.getGid());
            pre.setString(4,zgoods.getSup_name());
            pre.setInt(5,zgoods.getZid());
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;

    }

    @Override
    public int delete(String id) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "delete from zgoods where zid=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,id);
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public int updateco(String zname,int count) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "update zgoods set zcount=? where zname=?";
        try {
           con = ConnectionFactory.getConnection();
           pre = con.prepareStatement(sql);
           pre.setInt(1,count);
           pre.setString(2,zname);
           row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public PageModel<Zgoods> findsc(int pageSize, int pageNo, int zcount) {
        ArrayList<Zgoods> list = new ArrayList<Zgoods>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select zid,zname,zcount,gid,sup_name from zgoods where zcount<" + zcount;
        PageModel<Zgoods> pm = new PageModel<Zgoods>(pageSize, pageNo, total(sql));
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql+" limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()){
                Zgoods zgoods = new Zgoods();
                zgoods.setZid(rs.getInt(1));
                zgoods.setZname(rs.getString(2));
                zgoods.setZcount(rs.getInt(3));
                zgoods.setGid(rs.getString(4));
                zgoods.setSup_name(rs.getString(5));
                list.add(zgoods);
            }
            pm.setData(list);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    @Override
    public PageModel<Zgoods> findAl(int pageSize, int pageNo, String zname, int stacount, int endcount) {
        String sql = "select zname,zcount,gid,sup_name from zgoods";
        if (!("".equals(zname) || zname == null)) {
            sql += " where zname like'%" + zname + "%'";
            if (stacount != 0 && endcount != 0) {
                sql += " and zcount between " + stacount + " and " + endcount;
            }
        } else if (stacount != 0 && endcount != 0) {
            sql += " where zcount between " + stacount + " and " + endcount;
        }

        ArrayList<Zgoods> reginfos = new ArrayList<Zgoods>();
        PageModel<Zgoods> pm = new PageModel<Zgoods>(pageSize, pageNo,
                total(sql));

        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql + " limit " + (pm.getPageNo() - 1)
                    * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()) {
                Zgoods zgoods = new Zgoods();
                zgoods.setZname(rs.getString(1));
                zgoods.setZcount(rs.getInt(2));
                zgoods.setGid(rs.getString(3));
                zgoods.setSup_name(rs.getString(4));
                reginfos.add(zgoods);
            }
            pm.setData(reginfos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    public int total(String sql) {
        int recordCount = 0;
        String countsql = "select count(*) "
                + sql.substring(sql.indexOf("from"), sql.length());

        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(countsql);
            if (rs.next()) {
                recordCount = rs.getInt(1);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if (rs != null){rs.close();}
                if (stmt != null){stmt.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recordCount;
    }
}
